A relational-XML data warehouse for data aggregation with SQL and XQuery

Integration of multiple data sources is becoming increasingly important for enterprises that cooperate closely with their partners for e-commerce. OLAP enables analysts and decision makers fast access to various materialized views from data warehouses. However, many corporations have internal business applications deployed on different platforms. No standard solution for integration exists, except to develop an autonomous system for large enterprises. To enable business intelligence query activities, a corporation needs to build a data warehouse on top of a middleware to aggregate summarized data from various heterogeneous database systems. Since XML documents are a common data representation standard on the Internet ― and relational tables are traditional production data ― OLAP must handle both relational and XML data on the World Wide Web. SQL and XQuery can be used to process the materialized relational and XML data cubes of aggregated data. This paper aims to facilitate handling these two data cubes from a Relational-XML data warehouse with ETL (Extract, Transformation and Loading). 1 Introduction To acquire business intelligence and analyses, a corporation must build a data warehouse to aggregate data (compute total) from a variety of heterogeneous database systems and sources. The traditional approach has been to build a relational data warehouse by using a full set of data warehouse tools of middleware, messaging and ETL tools. This paper offers a methodology to develop a heterogeneous relational-XML data warehouse by using OLAP in SQL and XQuery. An ETL methodology is provided to summarize information from heterogeneous database systems using relational and XML data. The approach is to integrate a company's business processes with its subsidiaries, partners, customers and suppliers. Thus, the system exposes data extraction, data integration and data materialization on the middleware to facilitate the following: ‰ To evaluate the use of schema translation between relational and XML databases ‰ To integrate Relational tables and XML documents for information aggregation ‰ To access the use of Relational-XML as a data warehouse ‰ To represent materialized views of XML data using XQuery[18], and of relational tables using SQL. To tackle these issues, a data warehouse on top of a middleware is a solution for system developers for such situations. Middleware integrates all the business applications to perform on-demand data integration, and stores the integrated data into a relational and an XML database. Two approaches exist to handle data integration on the middleware. The first approach is direct database access, which acquires …